/**
 * @author zhounan 
 * @date 2024年5月22日
 * @version V1.0
 */
package org.zn.etl.jdbc;

import java.util.List;
import java.util.function.Supplier;

import org.apache.commons.lang3.StringUtils;
import org.zn.datax.common.entity.TableInfo;
import org.zn.etl.job.context.GlobalContextKey;
import org.zn.etl.job.handler.LoadModeEnum;
import org.zn.etl.misc.DriverClassType;

/**
 * @author zhounan
 * @date 2024年5月22日
 * @version V1.0
 */
public class SqlBuilder {

	public static String tableSelectSql(String  pageMode,String loadMode,String dbType, TableInfo table, List<String> srcColNames) {

		String tableName = table.getSchema() + "." + table.getTable();

		String sql = null;

		String where = table.getWhere();
		String createWhere = table.getCreateTimeKey();
		String updateWhere = table.getModifyTimeKey();

		final StringBuilder whereStatment = new StringBuilder();

		boolean w = StringUtils.isNotEmpty(where);
		boolean c = StringUtils.isNotEmpty(createWhere);
		boolean u = StringUtils.isNotEmpty(updateWhere);
		
		boolean incMode= LoadModeEnum.increase.name().equals(loadMode);   

		if (w || incMode) {
			whereStatment.append(" where ");
			if (w) {
				whereStatment.append(w);
			}
		}

		Supplier<String> su =() ->""; 
		if(incMode) {
			su=() -> {
				
				if (!w)whereStatment.append(" 1=1 ");
				
				if (c) {
					whereStatment.append(" and ");
					whereStatment.append(createWhere).append(" > ${#").append(createWhere).append("} ");
				}
				if (u) {
					whereStatment.append(" and ");
					
					whereStatment.append(updateWhere).append(" > ${#").append(updateWhere).append("}");
				}
				return whereStatment.toString();
			};
			
		}

		if (DriverClassType.oracle.getDbType().equals(dbType)) {
		if(incMode) {	
			su=() -> {
				if (!w)whereStatment.append(" 1=1 ");
				if (c) {
					
					whereStatment.append(" and ");
					whereStatment.append(createWhere).append(" > TO_DATE(${#").append(createWhere)
							.append("}, 'YYYY-MM-DD') ");
				}
				if (u) {
					
					whereStatment.append(" and ");
					whereStatment.append(updateWhere).append(" > TO_DATE(${#").append(updateWhere)
							.append("}, 'YYYY-MM-DD') ");
				}
				return whereStatment.toString();
			};
		}

			sql = SqlBuilder.oracle(tableName, srcColNames,su);			
		
		} else if (DriverClassType.mysql.getDbType().equals(dbType)) {
			sql = SqlBuilder.mysql(tableName, srcColNames, su);
		} else if (DriverClassType.pg.getDbType().equals(dbType)) {
			sql = SqlBuilder.postgresql(tableName, srcColNames, su);
		}

		return sql;
	}
	
	

	public static String oracleSubSeclt(String sql) {

		StringBuilder sb = new StringBuilder();
		sb.append("SELECT * FROM  (  " + "	SELECT  page.*,ROWNUM rnum  FROM  " + "(");
		sb.append(sql);
		sb.append(" ) page  WHERE ROWNUM <= ${#" + GlobalContextKey.END_OFFSET + "} ) " + "WHERE rnum > ${#"
				+ GlobalContextKey.START_OFFSET + "}");
		return sb.toString();

	}

	public static String oracle(String table, List<String> items, Supplier<String> whereFunc) {

		StringBuilder sb = new StringBuilder();
		sb.append("SELECT * FROM  (  " + "	SELECT  page.*,ROWNUM rnum  FROM  " + "		( SELECT ");

		for (String it : items) {
			sb.append(it).append(",");
		}

		sb.append("ROWNUM rno ").append(" FROM ").append(table);
		sb.append(whereFunc.get());

		sb.append(" ) page  WHERE ROWNUM <= ${#" + GlobalContextKey.END_OFFSET + "} ) " + "WHERE rnum > ${#"
				+ GlobalContextKey.START_OFFSET + "}");
		return sb.toString();
	}
	
	public static String oracleBase(String table, List<String> items, Supplier<String> whereFunc) {

		StringBuilder sb = new StringBuilder();
		sb.append("SELECT");
		for (String it : items) {
			sb.append(it).append(",");
		}
		sb.append(" FROM ").append(table);
		sb.append(whereFunc.get());

		return sb.toString();
	}

	public static String mysql(String table, List<String> items, Supplier<String> whereFunc) {

		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ");
		for (String item : items) {

			sb.append(item).append(",");

		}

		sb.deleteCharAt(sb.length()-1);
		sb.append(" FROM ").append(table).append(whereFunc.get())
				.append(" limit ${#" + GlobalContextKey.START_OFFSET + "},${#" + GlobalContextKey.PAGED_SIZE + "}");

		return sb.toString();

	}

	public static String postgresql(String table, List<String> items, Supplier<String> whereFunc) {

		StringBuilder sb = new StringBuilder();
		sb.append("SELECT");
		for (String item : items) {

			sb.append(item).append(",");

		}

		sb.deleteCharAt(sb.length());
		sb.append(" FROM ").append(table).append(whereFunc.get()).append(
				"LIMIT  ${#" + GlobalContextKey.PAGED_SIZE + "} OFFSET  ${#" + GlobalContextKey.START_OFFSET + "}");

		return table;

	}

	public static void main(String[] args) {

		String sql = SqlBuilder.oracleSubSeclt(
				"SELECT a.* , oper.CREATEDATE, oper.CREATORID, oper.UPDATEDATE, oper.UPDATERID, ROWNUM rno FROM B_ATTACHINFO a LEFT JOIN B_OPERATORINFO oper ON a.REGISTRATIONID = oper.REGISTRATIONID ");

		System.out.println(sql);

	}

}
